﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

namespace SmartLibraries.DataAccessLayer
{
	public abstract class SmartBaseDal<Connection, Command, SmartException, Parameter, Adapter> : ISmartBaseDal
		where Connection : IDbConnection
		where Command : IDbCommand
		where SmartException : DbException
		where Parameter : DbParameter
		where Adapter : DbDataAdapter
	{

		#region Protected Properties For Derived Classes
			protected Connection objConnection;
			protected Command objCommand;
		#endregion


		protected SmartBaseDal(CommandType objCommandType,
			ISmartQuery objQuery)
			{
					this.objConnection = this.GetConnection(objQuery.Connectionstring);
					this.objConnection.Open();
					this.objCommand =  this.GetCommand(objQuery.Parameters);
					this.objCommand.CommandType = objCommandType;
					this.objCommand.CommandText = objQuery.Query;
			}

		protected virtual Connection GetConnection(string strPriConnectionString)
		{
			try
			{
				return (Connection)Activator.CreateInstance(typeof(Connection), strPriConnectionString);
			}
			catch(Exception e)
			{
				throw e.InnerException;
			}
		}
		protected virtual Command GetCommand(SmartDbParameters objParamters)
		{
			objCommand = (Command)objConnection.CreateCommand();
			if(objParamters != null)
			{
				foreach(SmartDbParameter objSmartParameter in objParamters)
				{
					Parameter objParameter = null;
					try
					{
						objParameter = (Parameter)Activator.CreateInstance(typeof(Parameter), objSmartParameter.StrParamterName, objSmartParameter.ObjParameterValue);
					}
					catch(Exception e)
					{
						throw e.InnerException;
					}
					objCommand.Parameters.Add(objParameter);
				}
			}
			return objCommand;
		}
		protected virtual int ExecuteNonQuery(Command objCommand)
		{
			Parameter objReturnValue = FunPriAddReturnParameter(objCommand);
			objCommand.ExecuteNonQuery();
			return FunPriReturnParameterValue(objReturnValue);
		}
		protected virtual int FillDataTable(Command objCommand, DataTable dtTemp)
		{
			Parameter objReturnValue = FunPriAddReturnParameter(objCommand);
			Adapter AdpPri = null;
			try
			{
				AdpPri = (Adapter)Activator.CreateInstance(typeof(Adapter), objCommand);
			}
			catch(Exception e)
			{
				throw e.InnerException;
			}
			AdpPri.Fill(dtTemp);
			return FunPriReturnParameterValue(objReturnValue);
		}
		protected virtual int FillDataSet(Command objCommand, DataSet dtTemp)
		{
			Parameter objReturnValue = FunPriAddReturnParameter(objCommand);
			Adapter AdpPri = null;
			try
			{
				AdpPri = (Adapter)Activator.CreateInstance(typeof(Adapter), objCommand);
			}
			catch(Exception e)
			{
				throw e.InnerException;
			}
			AdpPri.Fill(dtTemp);
			return FunPriReturnParameterValue(objReturnValue);
		}

		private static int FunPriReturnParameterValue(Parameter objReturnValue)
		{
			return objReturnValue == null ? 0 : Convert.ToInt32(objReturnValue.Value);
		}


		protected virtual int GetSingleRow(Command objCommand, SingleRow objRow)
		{
			DataTable dtSource = new DataTable();
			int intResult = this.FillDataTable(objCommand, dtSource);

			if(dtSource.Rows.Count > 0)
			{
				objRow.HasValue = true;
				for(int i = 0; i < dtSource.Columns.Count; i++)
				{
					objRow.Add(dtSource.Columns[i].ColumnName, dtSource.Rows[0][i]);
				}
			}
			else
			{
				objRow.HasValue = false;
				for(int i = 0; i < dtSource.Columns.Count; i++)
				{
					objRow.Add(dtSource.Columns[i].ColumnName, dtSource.Columns[i].DefaultValue);
				}
			}
			return intResult;
		}
		protected virtual Parameter FunPriAddReturnParameter(Command objCommand)
		{
			Parameter objReturnValue = null;
			try
			{
				objReturnValue = (Parameter)Activator.CreateInstance(typeof(Parameter), "@RETURN_VALUE", SqlDbType.Int);		
			}
			catch(Exception e)
			{
				throw e.InnerException;
			}
				

			objReturnValue.Direction = ParameterDirection.ReturnValue;
			objCommand.Parameters.Add(objReturnValue);
			return objReturnValue;
		}

		protected virtual void FunPriRaiseError(Command objCommand)
		{
			DataSet DsPriResult = new DataSet();
			this.FillDataSet(objCommand, DsPriResult);
			DataTable dtResult = DsPriResult.Tables[DsPriResult.Tables.Count - 1];

			this.FunPriRaiseError(dtResult);
		}
		protected virtual void FunPriRaiseError(DataTable dtResult)
		{
			DataRow dr = dtResult.Rows[0];
			throw new ClsDatabaseException
				(dr.Field<int>("ErrorNumber"), dr.Field<int>("ErrorSeverity"), dr.Field<int>("ErrorState"),
				dr.Field<string>("ErrorProcedure"), dr.Field<int>("ErrorLine"), dr.Field<string>("ErrorMessage"));
		}
		protected virtual void FunPriRaiseError(SingleRow dtResult)
		{
				throw new ClsDatabaseException
				(dtResult.GetField<int>("ErrorNumber"), dtResult.GetField<int>("ErrorSeverity"), dtResult.GetField<int>("ErrorState"),
				dtResult.GetField<string>("ErrorProcedure"), dtResult.GetField<int>("ErrorLine"), dtResult.GetField<string>("ErrorMessage"));
		}

		protected abstract void FunPriRaiseError(SmartException objException);
		#region Public functions
			public int ExecuteNonQuery()
			{
				try
				{
					int intReturnValue = this.ExecuteNonQuery(objCommand);
					return intReturnValue;
				}
				catch(SmartException objException)
				{
					FunPriRaiseError(objException);
				}
				return -1;				
			}
			public DataTable GetDataTable()
			{
				DataTable dtTemp = new DataTable();				
				try
				{
					int intReturnValue = this.FillDataTable(objCommand, dtTemp);
					//if(intReturnValue == -1)
					//{
					//    FunPriRaiseError(dtTemp);
					//    return null;
					//}
					//else
					{
						return dtTemp;
					}
				}
				catch(SmartException objException)
				{
					FunPriRaiseError(objException);
				}
				return null;				
			}
			public DataSet GetDataSet()
			{
				DataSet DsPriTemp = new DataSet();
				try
				{
					int intReturnValue = this.FillDataSet(objCommand, DsPriTemp);
					//if(intReturnValue == -1)
					//{
					//    FunPriRaiseError(DsPriTemp.Tables[0]);
					//    return null;
					//}
					//else
					{
						return DsPriTemp;
					}
				}
				catch(SmartException objException)
				{
					FunPriRaiseError(objException);
				}
				return null;
			}
			public SingleRow GetSingleRow()
			{
				SingleRow objRow = new SingleRow();
				try
				{
					int intReturnValue = this.GetSingleRow(objCommand, objRow);
					//if(intReturnValue == -1)
					//{
					//    FunPriRaiseError(objRow);
					//    return null;
					//}
					//else
					{
						return objRow;
					}
				}
				catch(SmartException objException)
				{
					FunPriRaiseError(objException);
				}
				return null;
			}
		#endregion
		#region IDisposable Members

		public void Dispose()
		{
			this.objConnection.Dispose();
			this.objCommand.Dispose();
		}

		#endregion
	}
}
